
-- **************************************************************************
--    Project Name:   运力支线网点到车准点率明细
--    Job Name:       jms_dm.dm_yl_shipment_branch_detail_dt
--    Author :        季修魁
--    date：          2023/11/21
-- **************************************************************************
-- **************************************************************************

---1.支线任务信息
with shipment_base_tab as (
select t.*
       ,net1.network_type   as start_network_type
       ,net1.agent_code     as start_agent_code
	   ,net1.agent_name     as start_agent_name
	   ,net1.virt_code      as start_virt_code
	   ,net1.virt_name      as start_virt_name
	   ,net1.fran_code      as start_fran_code
	   ,net1.fran_name      as start_fran_name
       ,net2.network_type   as end_network_type
       ,net2.agent_code     as end_agent_code
	   ,net2.agent_name     as end_agent_name
  from (
select id as shipment_id
       ,shipment_no
       ,shipment_name
       ,shipment_state
       ,cnt
       ,start_code
       ,start_name
       ,end_code
       ,end_name
       ,planned_departure_time
       ,actual_departure_time
  from jms_dwd.dwd_tmsnew_shipment_union_base_dt
 where dt>=date_add('{{ execution_date | cst_ds }}',-15) and dt<=date_add('{{ execution_date | cst_ds }}',15)
   and shipment_state <> 5
   and table_type=2   ---支线
   ) t
  left join jms_dim.dim_network_whole_massage net1
    on t.start_code= net1.code
  left join jms_dim.dim_network_whole_massage net2
    on t.end_code= net2.code
 ),

---2.线路经停站点信息
shipment_end_stop_d_tab as (
select shipment_no                     ---任务code
       ,shipment_id                    ---任务id
	   ,sort_num                       ---顺序编号
	   ,network_code                   ---经停网点编号
	   ,network_name                   ---经停网点名称
       ,load_start_time                ---装车发件时间
       ,lock_time                      ---封发车
	   ,actual_departure_time          ---实际发车时间
       ,planned_departure_time         ---计划发车时间
       ,planned_arrival_time           ---计划到车时间
       ,actual_arrival_time            ---实际到车时间
       ,estimate_arrival_time          ---预计到达时间
       ,unlock_time                    ---到解车
       ,unload_start_time              ---卸车到件
       ,app_arrival_time               ---大驾好打卡时间
  from jms_dwd.dwd_tmsnew_shipment_stop_dt
 where dt='{{ execution_date | cst_ds }}'
  ),

----整合res
shipment_zx_dtl_tab_res as (
  select to_date(t.actual_departure_time)    as date_time
         ,t.shipment_id                    as shipment_id
         ,t.shipment_no                    as shipment_no
		 ,t.shipment_name                  as shipment_name
		 ,t.shipment_state                 as shipment_state
		 ,t.cnt                            as shipment_cnt
		 ,t.start_code                     as start_network_code
		 ,t.start_name                     as start_network_name
         ,t.start_network_type             as start_network_type
         ,t.start_agent_code	           as start_agent_code
         ,t.start_agent_name	           as start_agent_name
         ,t.start_virt_code                as start_virt_code
         ,t.start_virt_name                as start_virt_name
         ,t.start_fran_code		           as start_fran_code
         ,t.start_fran_name		           as start_fran_name
		 ,t.end_code                       as end_network_code
		 ,t.end_name                       as end_network_name
		 ,t.end_network_type               as end_network_type
		 ,t.end_agent_code                 as end_agent_code
		 ,t.end_agent_name	               as end_agent_name
         ,stop1.lock_time                  as lock_time
		 ,stop1.load_start_time            as load_start_time
         ,stop1.planned_departure_time     as planned_departure_time
         ,stop1.actual_departure_time	   as actual_departure_time
		 ,stop2.planned_arrival_time       as planned_arrival_time
		 ,stop2.actual_arrival_time        as actual_arrival_time
		 ,stop2.estimate_arrival_time      as estimate_arrival_time
		 ,stop2.unlock_time                as unlock_time
		 ,stop2.unload_start_time          as unload_start_time
		 ,stop2.app_arrival_time           as app_arrival_time
    from shipment_base_tab t
    left join shipment_end_stop_d_tab stop1
      on t.shipment_no = stop1.shipment_no
     and stop1.sort_num=1
    left join shipment_end_stop_d_tab stop2
      on t.shipment_no = stop2.shipment_no
     and t.cnt = stop2.sort_num
)

insert overwrite table jms_dm.dm_yl_shipment_branch_detail_dt  partition (dt)
SELECT  date_time
       ,shipment_id
	   ,shipment_no
	   ,shipment_name
	   ,shipment_state
	   ,shipment_cnt
	   ,start_network_code
	   ,start_network_name
	   ,start_network_type
	   ,start_agent_code
	   ,start_agent_name
       ,start_virt_code
       ,start_virt_name
	   ,start_fran_code
	   ,start_fran_name
	   ,end_network_code
	   ,end_network_name
	   ,end_network_type
	   ,end_agent_code
	   ,end_agent_name
	   ,lock_time
	   ,load_start_time
	   ,planned_departure_time
	   ,actual_departure_time
	   ,planned_arrival_time
	   ,actual_arrival_time
	   ,estimate_arrival_time
	   ,unlock_time
	   ,unload_start_time
	   ,app_arrival_time
	   ,case when start_network_type in (6,5) and end_network_type =4 then 1 else 0 end as  is_shipment_flag
	   ,case when start_network_type in (6,5) and end_network_type =4 and unix_timestamp(planned_arrival_time)>=unix_timestamp(actual_arrival_time)
	          then 1 else 0 end as  is_ontime_flag
	   ,date_time as dt
  from shipment_zx_dtl_tab_res res
 where to_date(actual_departure_time) >=date_sub('{{ execution_date | cst_ds }}',7)
   and to_date(actual_departure_time) <='{{ execution_date | cst_ds }}'
---  and start_network_type in (6,5)     ---网点
---  and end_network_type =4             ---中心
distribute by dt,pmod(hash(rand()),4)
;